package com.csust.base;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class jdbcOperation {
    @Test
    //Single row and col
    public void test() throws Exception {
        //1. 省略
        //2.连接                                                  //jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");
        //3. PreparedStatement
        PreparedStatement ps = connection.prepareStatement("select count(*) as count from `t_emp`");

        //4.
        ResultSet rs = ps.executeQuery();
        //5.
        while (rs.next()) {
            System.out.println(rs.getInt("count"));
        }

        //6.
        rs.close();
        ps.close();
        connection.close();
    }

    @Test
    //Single row but cols
    public void test1() throws Exception {
        //1.
        //2.
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");

        //3.
        PreparedStatement ps = connection.prepareStatement("SELECT `emp_id`,`emp_name`,`emp_salary`,`emp_age` FROM `t_emp`\n" +
                "WHERE `emp_id` = ?");

        //4.
//        System.out.println("请输入你要查询的ID：");
//        Scanner sc = new Scanner(System.in);
//        int id = sc.nextInt();
        ps.setInt(1,5);


        ResultSet rs = ps.executeQuery();

        //5.
        if(rs.next()){
            int empId = rs.getInt("emp_id");
            String empName = rs.getString("emp_name");
            Double empSalary = rs.getDouble("emp_salary");
            int empAge = rs.getInt("emp_age");
            System.out.println(empId + "\t" + empName + "\t" + empSalary + "\t" + empAge);
        }
        //6.

        rs.close();
        ps.close();
        connection.close();

    }


    @Test
    //lots rows and lots cols
    public void test2() throws Exception {
        //1.
        //2.                                             //jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");



        //3.
        PreparedStatement ps = conn.prepareStatement("select * from `t_emp` where `emp_age` >?");


        //4.
        ps.setInt(1,25);


        ResultSet rs = ps.executeQuery();


        while(rs.next()){
            int empId = rs.getInt("emp_id");
            String empName = rs.getString("emp_name");
            Double empSalary = rs.getDouble("emp_salary");
            int empAge = rs.getInt("emp_age");
            System.out.println(empId + "\t" + empName + "\t" + empSalary + "\t" + empAge);
        }

        //6.
        rs.close();
        ps.close();
        conn.close();



    }

    @Test
    //Insert
    public void test3() throws Exception {

        //1.
        //2.
        Connection  conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");
        //3.
        PreparedStatement ps = conn.prepareStatement("insert into `t_emp` (`emp_name`,`emp_salary`,`emp_age`)\n" +
                "values (?,?,?)");
        //4.
//        Scanner sc = new Scanner(System.in);
//        String name = sc.nextLine();
//        Double salary = sc.nextDouble();
//        int age = sc.nextInt();


        ps.setString(1,"sail");
        ps.setDouble(2,20000.00);
        ps.setInt(3,20);

        int result = ps.executeUpdate();

        //5.
        if(result>0){
            System.out.println("成功！");
        }
        else {
            System.out.println("失败！");
        }



        //6.
//        rs.close();
        ps.close();
        conn.close();




    }

    //Update
    @Test
    public void test4() throws Exception {
        //1.
        //2.
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");
        //3.
        PreparedStatement ps = conn.prepareStatement("update  `t_emp` set `emp_salary` = ? where `emp_id` = ?");

        //4.
        ps.setDouble(1,25000.00);
        ps.setInt(2,6);

        int result = ps.executeUpdate();

        //5.
        if(result>0){
            System.out.println("成功！");
        }else{
            System.out.println("失败！");
        }

        //6.
        ps.close();
        conn.close();

    }
    //Delete
    @Test
    public void test5() throws Exception {
        //1.
        //2.
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu?useUnicode=true&characterEncoding=utf8&useSSL=false","root","123456");
        //3.
        PreparedStatement ps = conn.prepareStatement("delete from `t_emp` where `emp_id` = ?");

        //4.
        ps.setInt(1,6);

        int result = ps.executeUpdate();

        //5.
        if(result>0){
            System.out.println("成功！");
        }else{
            System.out.println("失败！");
        }

        //6.
        ps.close();
        conn.close();






    }
}
